<?php
include_once ('DataBase.php');
include_once ('User.php');
include_once ('Ticket.php');

class DB_For_User extends DataBase {

	const USERNAME = 'root';

	const PASSWORD = '';

	const USER_DEFAULT_PASSWORD = 'default';

	public function __construct() {
		parent::__construct($this::USERNAME, $this::PASSWORD);
	}

	/**
	 *
	 * @param
	 *        	unescaped string $email
	 * @return integer 1=user exists, 0=user don't exists, -1=error
	 */
	private function isUserExists($email) {
		if(is_string($email)) {
			try {
				$querry = "SELECT `id` FROM `user` WHERE `email` = ?";
				$stmt = $this->db->prepare($querry);
				if($stmt) {
					$email = mysql_real_escape_string($email);
					$stmt->bind_param('s', $email);
					$stmt->execute();
					$result = $stmt->get_result();
					if($result) {
						if($result->num_rows > 0) {
							return 1;
						} else {
							return 0;
						}
					}
				}
			} catch(Exception $e) {
				return -1;
			}
		}
		return -1;
	}

	/**
	 *
	 * @param string $email
	 *        	unescaped
	 * @param string $password        	
	 * @param integer $grant        	
	 * @return boolean
	 */
	public function createUser($email, $password, $grant = User::VISITOR_INT) {
		if(is_string($email) && is_string($password) && $this->isUserExists($email) == 0) {
			try {
				$querry = "INSERT INTO user (email, password, fk_type) VALUES (?, ?, ?)";
				$stmt = $this->db->prepare($querry);
				if($stmt) {
					$email = mysql_real_escape_string($email);
					$password = md5($password);
					$grant = intval($grant);
					
					$stmt->bind_param('ssi', $email, $password, $grant);
					if($stmt->execute()) {
						return true;
					}
				}
			} catch(Exception $e) {
				return false;
			}
		}
		return false;
	}

	/**
	 *
	 * @param string $email        	
	 * @return User array NULL
	 */
	private function searchUser($email, $orderBy = 'email', $order = 'ASC') {
		switch($orderBy) {
			case 'id' :
				break;
			case 'email' :
				break;
			case 'rights' :
				break;
			default :
				$orderBy = 'email';
				break;
		}
		switch($order) {
			case 'ASC' :
				break;
			case 'DESC' :
				break;
			default :
				$order = 'ASC';
				break;
		}
		try {
			$querry = "SELECT `id`, `email`, `fk_type` FROM `user` WHERE `email` LIKE ? ORDER BY ".$orderBy." ".$order;
			
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$email = '%'.mysql_real_escape_string($email).'%';
				$stmt->bind_param('s', $email);
				if($stmt->execute()) {
					$result = $stmt->get_result();
						if($result) {
							$users = Array();
								while($row = $result->fetch_object()) {
									$user_id = $row->id;
									$email = stripslashes($row->email);
									$grant = intval($row->fk_type);
		
									$users[] = new User($user_id, $email, $grant);
								}
						}
				}
				return $users;
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}

	/**
	 *
	 * @param integer $user_Id        	
	 * @return User NULL
	 */
	public function getUserById($user_Id) {
		try {
			$querry = "SELECT `id`, `email`, `fk_type` FROM `user` WHERE `id` = ?";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$user_Id = intval($user_Id);
				$stmt->bind_param('i', $user_Id);
				if($stmt->execute()) {
					$result = $stmt->get_result();
					if($result) {
						if($result->num_rows == 1) {
							$row = $result->fetch_object();
							$email = stripslashes($row->email);
							$grant = intval($row->fk_type);
							
							$user = new User($user_Id, $email, $grant);
							return $user;
						}
					}
				}
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}

	/**
	 *
	 * @param string $email
	 *        	unescaped
	 * @param string $password        	
	 * @return integer NULL
	 */
	public function getIdFromUser($email, $password) {
		if(is_string($email) && is_string($password)) {
			try {
				$querry = "SELECT `id` FROM `user` WHERE `email` = ? and `password` = ?";
				$stmt = $this->db->prepare($querry);
				if($stmt) {
					$email = mysql_real_escape_string($email);
					$password = md5($password);
					$stmt->bind_param('ss', $email, $password);
					if($stmt->execute()) {
						$result = $stmt->get_result();
						if($result) {
							if($result->num_rows == 1) {
								$row = $result->fetch_object();
								$user_id = $row->id;
								return $user_id;
							}
						}
					}
				}
			} catch(Exception $e) {
				return null;
			}
		}
		return null;
	}
	
	/**
	 *
	 * @return multitype:User |NULL
	 */
	public function getAllUsers($email = null, $orderBy = 'email', $order = 'ASC') {
		switch($orderBy) {
			case 'id' :
				break;
			case 'email' :
				break;
			case 'rights' :
				break;
			default :
				$orderBy = 'email';
				break;
		}
		switch($order) {
			case 'ASC' :
				break;
			case 'DESC' :
				break;
			default :
				$order = 'ASC';
				break;
		}
		if($email == null OR !isset($email)) {
			try {
				$querry = "SELECT `id`, `email`, `fk_type` FROM `user` ORDER BY ".$orderBy." ".$order;
				$result = $this->db->query($querry);
				if($result) {
					$users = Array();
					while($row = $result->fetch_object()) {
						$user_id = $row->id;
						$email = stripslashes($row->email);
						$grant = intval($row->fk_type);
						
						$users[] = new User($user_id, $email, $grant);
					}
					return $users;
				}
			} catch(Exception $e) {
				return null;
			}
			return null;
		} else {
			return $this->searchUser($email, $orderBy, $order);
		}
	}

	/**
	 *
	 * @param integer $grant        	
	 * @return multitype:User |NULL
	 */
	public function getUsersByGrant($grant = User::VISITOR_INT) {
		try {
			$querry = "SELECT `id`, `email`, `fk_type` FROM `user` WHERE `fk_type` = ?";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$grant = intval($grant);
				$stmt->bind_param('i', $grant);
				if($stmt->execute()) {
					$result = $stmt->get_result();
					if($result) {
						$users = Array();
						while($row = $result->fetch_object()) {
							$user_id = $row->id;
							$email = stripslashes($row->email);
							$grant = intval($row->fk_type);
							
							$users[] = new User($user_id, $email, $grant);
						}
						return $users;
					}
				}
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}
	
	public function getTicketById($id) {
		try {
			$querry = "SELECT t.`id`, t.`titel`, t.`description`, t.`comments`, t.`solution`, t.`sla`, t.`create_time`, u.email as fk_creator , ca.description as fk_category, p.description as fk_priority, s.description as fk_status  FROM `ticket` t join user u on `fk_creator` = u.id join category ca on `fk_category` = ca.id join priority p on fk_priority = p.id join status s on fk_status = s.id WHERE t.`id` = ?";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$stmt->bind_param('i', $id);
				if($stmt->execute()) {
					$result = $stmt->get_result();
					if($result) {
						if($result->num_rows == 1) {
							$row = $result->fetch_object();
							$id = $row->id;
							$titel = $row->titel;
							$description = $row->description;
							$comments = $row->comments;
							$solution = $row->solution;
							$sla = $row->sla;
							$create_time = $row->create_time;
							$creator = $row->fk_creator;
							$priority = $row->fk_priority;
							$status = $row->fk_status;
							$category = $row->fk_category;
								
							$ticket = new Ticket($id, $titel, $description, $comments, $solution, $sla, $create_time, $creator, $priority, $status, $category);
							return $ticket;
						}
					}
				}
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}
	
	public function getAllTickets($id) {
		try {
			$querry = "SELECT t.`id`, t.`titel`, t.`description`, t.`comments`, t.`solution`, t.`sla`, t.`create_time`, u.email as fk_creator , ca.description as fk_category, p.description as fk_priority, s.description as fk_status  FROM `ticket` t join user u on `fk_creator` = u.id join category ca on `fk_category` = ca.id join priority p on fk_priority = p.id join status s on fk_status = s.id WHERE `fk_creator` = ?";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$stmt->bind_param('i', $id);
				if($stmt->execute()) {
					$result = $stmt->get_result();
					if($result) {
						$tickets = Array();
						while($row = $result->fetch_object()) {
							$id = $row->id;
							$titel = $row->titel;
							$description = $row->description;
							$comments = $row->comments;
							$solution = $row->solution;
							$sla = $row->sla;
							$create_time = $row->create_time;
							$creator = $row->fk_creator;
							$priority = $row->fk_priority;
							$status = $row->fk_status;
							$category = $row->fk_category;
							
							$tickets[] = new Ticket($id, $titel, $description, $comments, $solution, $sla, $create_time, $creator, $priority, $status, $category);
						}
						return $tickets;
					}
				}
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}
	
	public function getAllOpenTickets() {
		try {
			$querry = "SELECT t.`id`, t.`titel`, t.`description`, t.`comments`, t.`solution`, t.`sla`, t.`create_time`, u.email as fk_creator , ca.description as fk_category, p.description as fk_priority, s.description as fk_status  FROM `ticket` t join user u on `fk_creator` = u.id join category ca on `fk_category` = ca.id join priority p on fk_priority = p.id join status s on fk_status = s.id WHERE `fk_status` = 1";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				if($stmt->execute()) {
					$result = $stmt->get_result();
					if($result) {
						$tickets = Array();
						while($row = $result->fetch_object()) {
							$id = $row->id;
							$titel = $row->titel;
							$description = $row->description;
							$comments = $row->comments;
							$solution = $row->solution;
							$sla = $row->sla;
							$create_time = $row->create_time;
							$creator = $row->fk_creator;
							$priority = $row->fk_priority;
							$status = $row->fk_status;
							$category = $row->fk_category;
								
							$tickets[] = new Ticket($id, $titel, $description, $comments, $solution, $sla, $create_time, $creator, $priority, $status, $category);
						}
						return $tickets;
					}
				}
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}
	
	public function getAllTakenTickets($id) {
		try {
			$querry = "SELECT t.`id`, t.`titel`, t.`description`, t.`comments`, t.`solution`, t.`sla`, t.`create_time`, u.email as fk_creator , ca.description as fk_category, p.description as fk_priority, s.description as fk_status  FROM `ticket` t left outer join editor e on t.id = e.ticket_id join user u on `fk_creator` = u.id join category ca on `fk_category` = ca.id join priority p on fk_priority = p.id join status s on fk_status = s.id WHERE e.`user_id` = ?";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$stmt->bind_param('i', $id);
				if($stmt->execute()) {
					$result = $stmt->get_result();
					if($result) {
						$tickets = Array();
						while($row = $result->fetch_object()) {
							$id = $row->id;
							$titel = $row->titel;
							$description = $row->description;
							$comments = $row->comments;
							$solution = $row->solution;
							$sla = $row->sla;
							$create_time = $row->create_time;
							$creator = $row->fk_creator;
							$priority = $row->fk_priority;
							$status = $row->fk_status;
							$category = $row->fk_category;
	
							$tickets[] = new Ticket($id, $titel, $description, $comments, $solution, $sla, $create_time, $creator, $priority, $status, $category);
						}
						return $tickets;
					}
				}
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}
	
	public function getAllCategories() {
		try {
			$querry = "SELECT id, description FROM category";
			$result = $this->db->query($querry);
			if($result) {
				$categories = Array();
				$i = 0;
				while($row = $result->fetch_object()) {
					$id = $row->id;
					$description = $row->description;
					
					$categories[$i]['id'] = $id;
					$categories[$i]['description'] = $description;
					$i++;
				}
				return $categories;
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}
	
	public function getAllPriorities() {
		try {
			$querry = "SELECT id, description FROM priority";
			$result = $this->db->query($querry);
			if($result) {
				$categories = Array();
				$i = 0;
				while($row = $result->fetch_object()) {
					$id = $row->id;
					$description = $row->description;
						
					$categories[$i]['id'] = $id;
					$categories[$i]['description'] = $description;
					$i++;
				}
				return $categories;
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}
	
	public function createTicket($titel, $description, $category, $priority, $creator) {
		try {
			$querry = "INSERT INTO ticket (titel, description, fk_creator, fk_priority, fk_status, fk_category) VALUES (?, ?, ?, ?, ?, ?)";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$titel = mysql_real_escape_string($titel);
				$description = mysql_real_escape_string($description);
				$status = 1; //Offen
				
				$stmt->bind_param('ssiiii', $titel, $description, $creator, $priority, $status, $category);
				if($stmt->execute()) {
					return true;
				}
			}
		} catch(Exception $e) {
			return false;
		}
	}
	
	public function takeTicket($id, $user_id) {
		try {
			parent::startTransaction();
			$querry = "INSERT INTO editor (ticket_id, user_id) VALUES (?, ?)";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$stmt->bind_param('ii', $id, $user_id);
				if($stmt->execute()) {
					$querry = "UPDATE ticket SET fk_status = 2 WHERE id = ?";
					$stmt = $this->db->prepare($querry);
					if($stmt) {
						$stmt->bind_param('i', $id);
						if($stmt->execute()) {
							parent::stopTransaction();
							return true;
						}
					}
				}
			}
		} catch(Exception $e) {
			parent::stopTransaction();
			return false;
		}
	}
	
	public function getComments($ticket_id) {
		try {
			$querry = "SELECT * FROM `ticket_has_comment` tc LEFT OUTER JOIN ticket t ON tc.ticket_id = t.id join comment c on tc.comment_id = c.id join user u on c.user_id = u.id  where t.id = ?";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$stmt->bind_param('i', $ticket_id);
				if($stmt->execute()) {
					$result = $stmt->get_result();
					if($result) {
						$comments = Array();
						$i = 0;
						while($row = $result->fetch_object()) {
							$email = $row->email;
							$value = $row->value;
							
							$comments[$i]['email'] = $email;
							$comments[$i]['value'] = $value;
							$i++;
						}
						return $comments;
					}
				}
			}
		} catch(Exception $e) {
			return null;
		}
		return null;
	}
	
	public function insertComment($ticket_id, $user_id, $comment) {
		try {
			parent::startTransaction();
			$querry = "INSERT INTO comment (user_id, value) VALUES(?, ?)";
			$stmt = $this->db->prepare($querry);
			if($stmt) {
				$comment = mysql_real_escape_string($comment);
				$stmt->bind_param('is', $user_id, $comment);
				if($stmt->execute()) {
					$querry = "INSERT INTO ticket_has_comment (ticket_id, comment_id) VALUES(?, LAST_INSERT_ID())";
					$stmt = $this->db->prepare($querry);
					if($stmt) {
						$stmt->bind_param('i', $ticket_id);
						if($stmt->execute()) {
							parent::stopTransaction();
							return true;
						}
					}
				}
			}
		} catch(Exception $e) {
			parent::stopTransaction();
			return false;
		}
	}
}
?>